import xlrd
import pymssql
import datetime
# 连接本地sql server         地址          用户名   密码     数据库Test_BmsDB_V2_20190807
from decimal import Decimal

conn = pymssql.connect("192.168.117.121", "sa", "bpr@doss@2015", "LotteryTest")
# 建立cursor
cursor = conn.cursor()
# excel文件
fname = "C:\\Users\\clq\\Desktop\\各岗位 应知应会知识词条 （总表3060条）.xlsx"
# fname = "C:\\Users\\clq\\Desktop\\词条1970.xls"
#打开文件
bk = xlrd.open_workbook(fname)
#打开工作表
sh = bk.sheets()[0]
#获取行数
# 遍历所有行
list = []
for i in range(1,sh.nrows):
    a = []
    # 遍历所有列
    for j in range(1,sh.ncols):
        val = str(sh.cell(i, j).value)
        a.append(val)
    list.append(a)
# print(list)
data_list = []
commment_sql = "INSERT INTO [Tbl_Entry] (Code,Entry,EntryDefine,Type,Station,Describe,Category) " \
               " VALUES   (%s,%s,%s,%s,%s,%s,%s)"
code = 20190910001
for i,item in enumerate(list):
    # Code = Decimal(item[0]).to_integral()
    # Entry = item[1]
    # EntryDefine = item[2]
    # Category = item[3]
    # Station = item[4]
    # Describe = item[5]
    #
    Code = Decimal(code+i).to_integral()
    Entry = item[0]
    EntryDefine = item[1]
    Category = item[2]
    Station = item[3]
    Describe = item[4]

    if '什么' in Entry or '哪' in Entry or '？'in Entry:
        Type = 1
    else:
        Type = 0
    data_list.append((Code,Entry,EntryDefine,Type,Station,Describe,Category))
cursor.executemany(commment_sql,data_list);
conn.commit()

